---------------------------v2--------------------------------

-- **************************************************************************
--    Project Name:   全程中转次数报表-明细
--    Job Name:       jms_dm.dm_all_transfer_count_deatil_dt
--    Author :        马诚
--    date：          2022/07/17
-- **************************************************************************
-- **************************************************************************
--    modify by 马诚 2022/7/17 新增脚本
-- **************************************************************************
with
--取一二段码逻辑
bill_three_code as (
   select
      t3.code
     ,t3.network_name
     ,t3.network_code
    from (
        select
             t3.code
            ,t3.network_name
            ,t3.network_code
    --         ,t3.center_name
    --         ,t3.center_code
            --,t4.code as first_code
            ,row_number() over(partition by t3.code order by t3.update_time desc) as rn
        from  jms_dim.dim_lmdm_sys_second_code t3
    --     left join (
    --                 select code,center_code from jms_dim.dim_lmdm_sys_first_code  t4  where  t4.is_enable='1' and t4.is_delete='1'  group by code,center_code
    --               ) t4 on t4.center_code= t3.center_code
        where  t3.is_enable='1' and t3.is_delete='1'
    ) t3 where t3.rn=1
)
--取退件,转寄件,原三段码
,bill_reback_base as (
    select
         t1.waybill_no --运单号
        ,max(t1.status ) status    --退件审核状态
        ,max(t1.origin_network_flag ) origin_network_flag--退件审核状态
        ,max(t1.examine_time  ) examine_time--审核时间
        ,max(t1.terminal_dispatch_code) terminal_dispatch_code  --原来的三段码
        ,max(t1.apply_type_code)   apply_type_code     --问题件code，转寄件取5
        ,max(t2.network_code)  terminal_network_code --原来的目的地
        ,max(t2.network_name)  terminal_network_name --原来的目的地

    from (
             select
                     t1.waybill_no --运单号
                    ,t1.status     --退件审核状态
                    ,t1.origin_network_flag --退件审核状态
                    ,t1.examine_time  --审核时间
                    ,t1.apply_type_code         --问题件code，转寄件取5
                    ,t1.terminal_dispatch_code  --原来的三段码
                    ,if(split(t1.terminal_dispatch_code,',')[0] ='' ,concat('9999','--',rand()), split(t1.terminal_dispatch_code,',')[0] ) as first_code
                    ,if(split(t1.terminal_dispatch_code,',')[1] ='' ,concat('9999','--',rand()), split(t1.terminal_dispatch_code,',')[1] ) as disp_code
             from  jms_dwd.dwd_tab_reback_transfer_express_base t1
        ) t1
        left join bill_three_code t2 on  t1.disp_code=t2.code
    group by t1.waybill_no

)
,must_center_step1 as (
 select /*+ BROADCAST(t)*/
     t.start_code  as start_code
    ,t1.name  as end_name
    ,tt.end_name as end_name1
    ,t2.network_type as end_name1_network_type
    ,nvl(tt.delivery_networklist,'99999')  as delivery_networklist
    ,sum( case when  t1.network_type = 4 then 1 else 0 end )over( partition by t.start_code) as network_flg
    ,t1.network_type
from
( select * from jms_dim.dim_yl_tms_branch_line_base_dt where  dt = '{{ execution_date |  cst_ds }}' distribute by pmod(hash(rand()), 20)) t

   left join jms_dim.dim_tms_branch_delivery_network_base_dt tt on t.id = tt.line_id
   left join jms_dim.dim_network_whole_massage t1 on t.end_code = t1.code
   left join jms_dim.dim_network_whole_massage t2 on t2.code = tt.end_code
where
     t.is_enable=1
    and tt.dt = '{{ execution_date |  cst_ds }}'

),must_center_step2 as (
    select
        t1.start_code,
        str_to_map(concat_ws(',', collect_set(t1.tmp_map))) as all_map,
        max(network_flg) as network_flg,
        if(max(null_end_name) ='',null,max(null_end_name)) as null_end_name,
        if(max(null_end_name1)='',null,max(null_end_name1)) as null_end_name1 --明细的目的网点
    from (
            select
              t.start_code
             ,all_end
             ,concat_ws(':', all_end,concat_ws('-', collect_set( case when t.network_type =4 then t.end_name end ))) tmp_map
             ,concat_ws('-', collect_set( case when t.delivery_networklist ='99999' and t.network_type =4 then t.end_name end )) as null_end_name
             ,concat_ws('-', collect_set( case when t.end_name1_network_type =4 then t.end_name1 end )) as null_end_name1

             ,max( network_flg )  network_flg
            from must_center_step1 t
            lateral view explode(split(t.delivery_networklist,',')) tb_id as all_end
           group by t.start_code,all_end
        ) t1
group by   t1.start_code
)
--,must_center_step2 as (
-- select
--     t.start_code  as start_code
--    ,t1.name  as end_name
--    ,tt.delivery_networklist as delivery_networklist
--from
--    jms_dim.dim_yl_tms_branch_line_base t
--   left join jms_dim.dim_tms_branch_delivery_network_base_dt tt on t.id = tt.line_id
--   left join jms_dim.dim_network_whole_massage t1 on t.end_code = t1.code
--where
--     t.is_enable=1
--    and tt.dt = '2023-03-15'
--    and t1.network_type = 4
--    and tt.delivery_networklist is not null
--)
--揽收网点,签收网点,始发中心，末端中心的扫描时间
,bill_first_center as (
  select  /*+ BROADCAST(t5),BROADCAST(t6)*/
     t2.waybill_no
    ,t2.first_center_arrival_early_time --始发中心最早到件时间
    ,t2.first_center_send_later_time    --始发中心最晚发车时间
    ,t2.end_center_send_later_time      --末端中心最早到件时间
    ,t2.end_center_arrival_early_time   --某段中心最晚发车时间
    ,nvl(t2.taking_early_scansitecode,t2.pick_network_code ) as  taking_early_scansitecode     --最早揽收网点
    ,t2.taking_early_scantime      as taking_time     --揽收时间
    ,tt.name as taking_name                 --揽收网点
    ,tt.network_type  as taking_network_type               --揽收网点类型，网点类型:1-总部,2-代理区,3-加盟商,4-中心,5-集散点,6-网点',
    ,tt.fran_code     as taking_fran_code                  --揽收网点加盟商
    ,tt.fran_name     as taking_fran_name                   --揽收网点加盟商code
    ,tt.agent_code    as taking_agent_code                  --'代理区编码',
    ,tt.agent_name    as taking_agent_name                 --'代理区名称,',
    ,tt.regional_id   as taking_manage_code                --'管理大区名称',
    ,tt.regional_desc   as taking_manage_name                --'管理大区名称',
    ,tt.provider_id   as taking_provider_id                --'省份ID',
    ,tt.provider_desc  as taking_provider_desc            --'省份',
    ,tt.city_id        as taking_city_id                   --'城市ID',
    ,tt.city_desc      as taking_city_desc                 --'城市',
    ,tt.area_id        as taking_area_id                   --'区/县ID',
    ,tt.area_desc      as taking_area_desc                 --'区/县',
    ,t2.first_center_network_code        --始发中心
    ,tt1.center_name   as first_center_network_name                 --始发中心
    ,t2.end_center_network_code
    ,tt2.center_name   as end_center_network_name                 --末端中心
    ,COALESCE(t2.sign_early_scantime,t2.outstore_early_scantime,instore_early_scantime,deliver_early_scantime) as sign_time--签收时间
    ,COALESCE(t2.sign_early_scansitecode,t2.outstore_early_scansitecode,t2.instore_early_scansitecode,t2.deliver_later_scansitecode) as sign_network_code
    ,tt3.name as sign_network_name  --签收网点
    ,tt3.network_type as sign_network_type
    ,tt3.fran_code     as sign_fran_code                  --签收网点加盟商
    ,tt3.fran_name     as sign_fran_name                   --签收网点加盟商code
    ,tt3.agent_code    as sign_agent_code                  --'代理区编码',
    ,tt3.agent_name    as sign_agent_name                 --'代理区名称,',
    ,tt3.regional_id   as sign_manage_code                --'管理大区名称',
    ,tt3.regional_desc   as sign_manage_name                --'管理大区名称',
    ,tt3.provider_id   as sign_provider_id                --'省份ID',
    ,tt3.provider_desc as sign_provider_desc            --'省份',
    ,tt3.city_id       as sign_city_id                   --'城市ID',
    ,tt3.city_desc     as sign_city_desc                 --'城市',
    ,tt3.area_id       as sign_area_id                   --'区/县ID',
    ,tt3.area_desc     as sign_area_desc                 --'区/县',
    --,concat_ws('-',collect_set(if( locate(concat(',',t2.end_center_network_code,','),t5.delivery_networklist)>0 ,t5.end_name,null) ) over(partition by t2.waybill_no,nvl(t2.taking_early_scansitecode,t2.pick_network_code ))) must_center_name
    ,CONCAT_WS('-',t5.null_end_name,nvl(t5.all_map[t2.end_center_network_code],t5.null_end_name1)
                  ,t6.null_end_name,nvl(t6.all_map[t2.end_center_network_code],t6.null_end_name1))  as must_center_name


    --,t2.first_nodal_network_code
    --,tt4.name as first_nodal_network_name
    ,if (greatest(t2.first_nodal_arrival_early_time,t2.first_nodal_send_later_time) < least(t2.first_center_arrival_early_time,t2.first_center_send_later_time),t2.first_nodal_network_code,null ) first_nodal_network_code
    ,if (greatest(t2.first_nodal_arrival_early_time,t2.first_nodal_send_later_time) < least(t2.first_center_arrival_early_time,t2.first_center_send_later_time),tt4.name,null ) first_nodal_network_name


    --新增
    ,if (greatest(t2.end_nodal_arrival_early_time,t2.end_nodal_send_later_time)>least(t2.first_center_arrival_early_time,t2.first_center_send_later_time),t2.end_nodal_network_code,null) as end_nodal_network_code
    ,if (greatest(t2.end_nodal_arrival_early_time,t2.end_nodal_send_later_time)>least(t2.first_center_arrival_early_time,t2.first_center_send_later_time),tt5.name ,null) as end_nodal_network_name

 from  jms_dwd.dwd_wide_sign_summary_waybill_dt t2
 left join jms_dim.dim_network_whole_massage  tt on nvl(t2.taking_early_scansitecode,t2.pick_network_code )=tt.code
 left join jms_dim.dim_network_whole_massage  tt3 on COALESCE(t2.sign_early_scansitecode,t2.outstore_early_scansitecode,t2.instore_early_scansitecode,t2.deliver_later_scansitecode)=tt3.code
 left join jms_dim.dim_network_whole_massage  tt4 on if(t2.first_nodal_network_code is null , t2.waybill_no,t2.first_nodal_network_code)=tt4.code
 left join jms_dim.dim_network_whole_massage  tt5 on if(t2.end_nodal_network_code is null , t2.waybill_no,t2.end_nodal_network_code)=tt5.code

 left join must_center_step2 t5 on  nvl(t2.taking_early_scansitecode,t2.pick_network_code )=t5.start_code       --应交件中心
 left join must_center_step2 t6 on  if(t2.first_nodal_network_code is null , t2.waybill_no,t2.first_nodal_network_code)=t6.start_code       --应交件中心

 left join (
             select
                center_name
                ,center_code
             from jms_dim.dim_network_whole_massage
             group by center_code
                       ,center_name
 ) tt1 on t2.first_center_network_code=tt1.center_code
 left join (
             select
                center_name
                ,center_code
             from jms_dim.dim_network_whole_massage
             group by center_code
                       ,center_name
 ) tt2 on t2.end_center_network_code=tt2.center_code
--  where   t2.dt>='2023-07-10'
 where   t2.dt='{{ execution_date |  cst_ds }}'


)

--问题件
,bill_problem_base as (
   select
    max(t4.proble_type_subject_name) as proble_type_subject_name --问题件code
   ,max(t4.proble_type_subject_code) as proble_type_subject_code --问题件
   ,t4.waybill_no
 from jms_dwd.dwd_sqs_registration_problem_piece_base_dt t4
join  jms_dim.dim_all_transfer_count_proble t5 on t4.proble_type_subject_code =t5.proble_code
 where  t4.dt>='{{ execution_date | date_add(-31) | cst_ds }}' and t4.dt<='{{ execution_date  | cst_ds }}'

 group by t4.waybill_no
 )
--取实际路由临时表，打标识计算A-B-C-B-C实际中转次数
,bill_base_shiji_tmp_step1 as (
        select
            t.waybill_no
           ,t.taking_early_scansitecode --揽收网点
           ,t.taking_name
           ,t.taking_time
           ,t.taking_network_type               --揽收网点类型，网点类型:1-总部,2-代理区,3-加盟商,4-中心,5-集散点,6-网点',
           ,t.taking_fran_code                  --揽收网点加盟商
           ,t.taking_fran_name                   --揽收网点加盟商code
           ,t.taking_agent_code                  --'代理区编码',
           ,t.taking_agent_name                 --'代理区名称,',
           ,t.taking_manage_code                --'管理大区名称',
           ,t.taking_manage_name                --'管理大区名称',
           ,t.taking_provider_id                --'省份ID',
           ,t.taking_provider_desc            --'省份',
           ,t.taking_city_id                   --'城市ID',
           ,t.taking_city_desc                 --'城市',
           ,t.taking_area_id                   --'区/县ID',
           ,t.taking_area_desc                 --'区/县',
           ,t.sign_network_code as  sign_network_code   --签收网点
           ,t.sign_network_name as   sign_network_name  --签收网点
           ,t.sign_network_type sign_network_type
           ,t.sign_time as sign_time                    --签收时间
           ,t.sign_fran_code    sign_fran_code             --签收网点加盟商
           ,t.sign_fran_name    sign_fran_name              --签收网点加盟商code
           ,t.sign_agent_code   sign_agent_code             --'代理区编码',
           ,t.sign_agent_name   sign_agent_name             --'代理区名称,',
           ,t.sign_manage_code  sign_manage_code            --'管理大区名称',
           ,t.sign_manage_name  sign_manage_name            --'管理大区名称',
           ,t.sign_provider_id  sign_provider_id            --'省份ID',
           ,t.sign_provider_desc sign_provider_desc         --'省份',
           ,t.sign_city_id       sign_city_id                  --'城市ID',
           ,t.sign_city_desc     sign_city_desc               --'城市',
           ,t.sign_area_id       sign_area_id                   --'区/县ID',
           ,t.sign_area_desc     sign_area_desc                --'区/县',
           ,t.first_center_network_code --始发中心
           ,t.first_center_network_name
           ,t.end_center_network_code  --末端中心
           ,t.end_center_network_name
           ,t.send_station_network_code  --发件网点
           ,t.send_station_simple_name  --发件网点简称
           ,t.send_flg
           ,t.send_station_network_name
           ,t.send_station_agent_code    --发件网点代理区
           ,t.send_station_network_type -- 发件网点类型
           ,t.step --站点排序
           ,if(t.send_station_network_code=
                lead(t.send_station_network_code,2)over(partition by t.waybill_no order by t.step),1,0 ) as lianxu_flg --连续排序

           ,concat(t.send_station_agent_code,'-',
                    lead(t.send_station_agent_code,1)over(partition by t.waybill_no order by t.step)) as lianxu_name  --连续站点拼接

           ,t.scan_time   --发件网点扫描时间
           ,t.examine_time --退转件审核时间
           ,t.terminal_dispatch_code --原三段码
           ,t.terminal_network_code  --原目的地
           ,t.terminal_network_name
           ,t.first_center_time --始发中心扫描时间
           ,t.end_center_time   --末端中心扫描时间
           ,t.sj_agent_in_count
           ,t.sj_agent_out_count
           ,row_number()over(partition by t.waybill_no order by t.step) as step_flg
           ,t.must_center_name
           ,t.first_nodal_network_code
           ,t.first_nodal_network_name
           ,t.end_nodal_network_code
           ,t.end_nodal_network_name
           ,t.dt

        from (
                    select
                         t.waybill_no
                        ,t.send_station_network_code  --发件网点
                        ,tt.simple_name as send_station_simple_name --发件网点简称
                        ,tt.name as send_station_network_name
                        ,tt.agent_code as   send_station_agent_code --发件网点代理区
                        ,tt.network_type as send_station_network_type -- 发件网点类型
                        ,case when tt.network_type=4 then 1 else 0 end send_flg
                        ,row_number()over(partition by t.waybill_no order by t.send_station_number) as step --站点排序
                        ,nvl(t.send_station_send_later_time,lag(t.next_station_arrival_early_time)over(partition by t.waybill_no order by t.send_station_number)) as  scan_time --到件时间
                        ,t.is_void
                        ,t.is_delete
                        ,nvl(case when t1.status=2 and t1.origin_network_flag =1 then t1.examine_time
                                  when t1.apply_type_code=5 then t1.examine_time
                               end ,'9999-99-99') as  examine_time --审核时间

                        ,nvl(case when t1.status=2 and t1.origin_network_flag =1 then t1.terminal_dispatch_code
                                  when t1.apply_type_code=5 then t1.terminal_dispatch_code
                                  end ,'9999-99-99') as terminal_dispatch_code --退转件原来三段码

                        ,nvl(case when t1.status=2 and t1.origin_network_flag =1 then t1.terminal_network_code
                                  when t1.apply_type_code=5 then t1.terminal_network_code
                                  end ,'9999-99-99') as terminal_network_code --退转件原的目的地

                        ,nvl(case when t1.status=2 and t1.origin_network_flag =1 then t1.terminal_network_name
                                  when t1.apply_type_code=5 then t1.terminal_network_name
                                  end ,'9999-99-99') as terminal_network_name --退转件原的目的地

                        ,nvl(least(t2.first_center_arrival_early_time,t2.first_center_send_later_time),'9999-99-99') as first_center_time --始发中心扫描时间
                        ,t2.first_center_network_code  --始发中心
                        ,t2.first_center_network_name
                        ,t2.end_center_network_code  --末端中心
                        ,t2.end_center_network_name
                        ,nvl(greatest(t2.end_center_send_later_time,t2.end_center_arrival_early_time),'9999-99-99') as end_center_time --目的中心扫描时间
                        ,t2.taking_early_scansitecode  --揽收网点
                        ,t2.taking_name
                        ,t2.taking_time                       --揽收时间
                        ,t2.taking_network_type               --揽收网点类型，网点类型:1-总部,2-代理区,3-加盟商,4-中心,5-集散点,6-网点',
                        ,t2.taking_fran_code                  --揽收网点加盟商
                        ,t2.taking_fran_name                   --揽收网点加盟商code
                        ,t2.taking_agent_code                  --'代理区编码',
                        ,t2.taking_agent_name                 --'代理区名称,',
                        ,t2.taking_manage_code                --'管理大区名称',
                        ,t2.taking_manage_name                --'管理大区名称',
                        ,t2.taking_provider_id                --'省份ID',
                        ,t2.taking_provider_desc            --'省份',
                        ,t2.taking_city_id                   --'城市ID',
                        ,t2.taking_city_desc                 --'城市',
                        ,t2.taking_area_id                   --'区/县ID',
                        ,t2.taking_area_desc                 --'区/县',
                        ,t2.sign_network_code as  sign_network_code   --签收网点
                        ,t2.sign_network_name as   sign_network_name  --签收网点
                        ,t2.sign_network_type sign_network_type
                        ,t2.sign_time as sign_time                    --签收时间
                        ,t2.sign_fran_code    sign_fran_code             --签收网点加盟商
                        ,t2.sign_fran_name    sign_fran_name              --签收网点加盟商code
                        ,t2.sign_agent_code   sign_agent_code             --'代理区编码',
                        ,t2.sign_agent_name   sign_agent_name             --'代理区名称,',
                        ,t2.sign_manage_code  sign_manage_code            --'管理大区名称',
                        ,t2.sign_manage_name  sign_manage_name            --'管理大区名称',
                        ,t2.sign_provider_id  sign_provider_id            --'省份ID',
                        ,t2.sign_provider_desc sign_provider_desc         --'省份',
                        ,t2.sign_city_id       sign_city_id                  --'城市ID',
                        ,t2.sign_city_desc     sign_city_desc               --'城市',
                        ,t2.sign_area_id       sign_area_id                   --'区/县ID',
                        ,t2.sign_area_desc     sign_area_desc                --'区/县',
                        ,case when tt.agent_code= t2.taking_agent_code and tt.network_type =4 then 1 else 0 end as sj_agent_in_count
                        ,case when tt.agent_code<>t2.taking_agent_code and tt.network_type =4 then 1 else 0 end as sj_agent_out_count
                        ,t2.must_center_name
                        ,t2.first_nodal_network_code
                        ,t2.first_nodal_network_name
                        ,t2.end_nodal_network_code
                        ,t2.end_nodal_network_name
                        ,t.dt

                    from jms_dwd.dwd_wide_sign_trace_waybill_step_dt t
                    left join jms_dim.dim_network_whole_massage  tt on t.send_station_network_code=tt.code
                    left join bill_reback_base t1  on  t.waybill_no=t1.waybill_no       --退回件
                    left join bill_first_center t2 on  t.waybill_no=t2.waybill_no       --取始发中心
                    where     t.dt='{{ execution_date |  cst_ds }}'


            ) t
            where --t.scan_time<=t.examine_time and
                 ( t.scan_time<=t.sign_time and t.scan_time>=t.first_center_time and t.scan_time<=t.end_center_time )
              or (t.first_center_time ='9999-99-99'  and t.end_center_time ='9999-99-99' )
)
--取实际路由临时表，打标识计算A-B-C-B-C实际中转次数，取出B-C段路由
,bill_base_shiji_tmp_step2 as (
    select
        t.lianxu_name  --连续站点拼接

       ,t.lianxu_flg
       ,t.waybill_no
       ,t.taking_early_scansitecode --揽收网点
       ,t.taking_name
       ,t.taking_time
       ,t.taking_network_type               --揽收网点类型，网点类型:1-总部,2-代理区,3-加盟商,4-中心,5-集散点,6-网点',
       ,t.taking_fran_code                  --揽收网点加盟商
       ,t.taking_fran_name                   --揽收网点加盟商code
       ,t.taking_agent_code                  --'代理区编码',
       ,t.taking_agent_name                 --'代理区名称,',
       ,t.taking_manage_code                --'管理大区名称',
       ,t.taking_manage_name                --'管理大区名称',
       ,t.taking_provider_id                --'省份ID',
       ,t.taking_provider_desc            --'省份',
       ,t.taking_city_id                   --'城市ID',
       ,t.taking_city_desc                 --'城市',
       ,t.taking_area_id                   --'区/县ID',
       ,t.taking_area_desc                 --'区/县',
       ,t.sign_network_code as  sign_network_code   --签收网点
       ,t.sign_network_name as   sign_network_name  --签收网点
       ,t.sign_network_type sign_network_type
       ,t.sign_time as sign_time                    --签收时间
       ,t.sign_fran_code    sign_fran_code             --签收网点加盟商
       ,t.sign_fran_name    sign_fran_name              --签收网点加盟商code
       ,t.sign_agent_code   sign_agent_code             --'代理区编码',
       ,t.sign_agent_name   sign_agent_name             --'代理区名称,',
       ,t.sign_manage_code  sign_manage_code            --'管理大区名称',
       ,t.sign_manage_name  sign_manage_name            --'管理大区名称',
       ,t.sign_provider_id  sign_provider_id            --'省份ID',
       ,t.sign_provider_desc sign_provider_desc         --'省份',
       ,t.sign_city_id       sign_city_id                  --'城市ID',
       ,t.sign_city_desc     sign_city_desc               --'城市',
       ,t.sign_area_id       sign_area_id                   --'区/县ID',
       ,t.sign_area_desc     sign_area_desc                --'区/县',
       ,t.first_center_network_code --始发中心
       ,t.first_center_network_name
       ,t.end_center_network_code --末端中心
       ,t.end_center_network_name
       ,t.send_station_network_code  --发件网点
       ,t.send_station_network_name
       ,t.send_station_simple_name
       ,concat(lpad(t.step,2,'0') ,':',t.send_station_network_name) as ws_send_station_network_name
       ,concat(lpad(t.step,2,'0') ,':',t.send_station_simple_name)  as ws_send_station_simple_name
       ,t.send_station_agent_code    --发件网点代理区
       ,t.send_station_network_type -- 发件网点类型
       ,sum(t.send_flg) over(partition by t.waybill_no) as send_flg1
       ,t.send_flg
       ,t.step --站点排序
       ,t.scan_time   --发件网点扫描时间
       ,t.examine_time --退转件取消时间
       ,t.terminal_dispatch_code --原三段码
       ,t.terminal_network_name
       ,t.terminal_network_code --原目的地
       ,t.first_center_time --始发中心扫描时间
       ,t.end_center_time   --末端中心扫描时间
       ,t.sj_agent_in_count
       ,t.sj_agent_out_count
       ,t.lianxu_sum
       ,t.step_flg
       ,t.step_site
       ,if(t.step_site=t.taking_early_scansitecode,1,0) as step_site_flg
       ,t.lianxu_sum1
       ,first_value(lianxu_name) over(partition by t.waybill_no order by lianxu_sum1 desc ,t.lianxu_flg desc ,t.step   ) as lianxu_name1
       ,t.must_center_name
       ,t.first_nodal_network_code
       ,t.first_nodal_network_name
       ,t.end_nodal_network_code
       ,t.end_nodal_network_name
       ,t.dt
    from (
            select
                    t.lianxu_name  --连续站点拼接

                   ,t.lianxu_flg
                   ,t.waybill_no
                   ,t.taking_early_scansitecode --揽收网点
                   ,t.taking_name
                   ,t.taking_time
                   ,t.taking_network_type               --揽收网点类型，网点类型:1-总部,2-代理区,3-加盟商,4-中心,5-集散点,6-网点',
                   ,t.taking_fran_code                  --揽收网点加盟商
                   ,t.taking_fran_name                   --揽收网点加盟商code
                   ,t.taking_agent_code                  --'代理区编码',
                   ,t.taking_agent_name                 --'代理区名称,',
                   ,t.taking_manage_code                --'管理大区名称',
                   ,t.taking_manage_name                --'管理大区名称',
                   ,t.taking_provider_id                --'省份ID',
                   ,t.taking_provider_desc            --'省份',
                   ,t.taking_city_id                   --'城市ID',
                   ,t.taking_city_desc                 --'城市',
                   ,t.taking_area_id                   --'区/县ID',
                   ,t.taking_area_desc                 --'区/县',
                   ,t.sign_network_code as  sign_network_code   --签收网点
                   ,t.sign_network_name as   sign_network_name  --签收网点
                   ,t.sign_network_type sign_network_type
                   ,t.sign_time as sign_time                    --签收时间
                   ,t.sign_fran_code    sign_fran_code             --签收网点加盟商
                   ,t.sign_fran_name    sign_fran_name              --签收网点加盟商code
                   ,t.sign_agent_code   sign_agent_code             --'代理区编码',
                   ,t.sign_agent_name   sign_agent_name             --'代理区名称,',
                   ,t.sign_manage_code  sign_manage_code            --'管理大区名称',
                   ,t.sign_manage_name  sign_manage_name            --'管理大区名称',
                   ,t.sign_provider_id  sign_provider_id            --'省份ID',
                   ,t.sign_provider_desc sign_provider_desc         --'省份',
                   ,t.sign_city_id       sign_city_id                  --'城市ID',
                   ,t.sign_city_desc     sign_city_desc               --'城市',
                   ,t.sign_area_id       sign_area_id                   --'区/县ID',
                   ,t.sign_area_desc     sign_area_desc                --'区/县',
                   ,t.first_center_network_code --始发中心
                   ,t.first_center_network_name
                   ,t.end_center_network_code --末端中心
                   ,t.end_center_network_name
                   ,t.send_station_network_code  --发件网点
                   ,t.send_station_network_name
                   ,t.send_station_simple_name
                    ,t.send_station_agent_code    --发件网点代理区
                    ,t.send_station_network_type -- 发件网点类型
                    ,t.send_flg
                   ,t.step --站点排序
                   ,t.scan_time   --发件网点扫描时间
                   ,t.examine_time --退转件取消时间
                   ,t.terminal_dispatch_code --原三段码
                   ,t.terminal_network_name
                   ,t.terminal_network_code --原目的地
                   ,t.first_center_time --始发中心扫描时间
                   ,t.end_center_time   --末端中心扫描时间
                     ,t.sj_agent_in_count
                    ,t.sj_agent_out_count

                   ,lianxu_sum
                   ,t.step_flg
                   ,t.step_site
                   ,sum(case when t.lianxu_flg =1 then 1 else 0 end ) over(partition by t.waybill_no,t.lianxu_sum ) as lianxu_sum1
                   ,t.must_center_name
                   ,t.first_nodal_network_code
                   ,t.first_nodal_network_name
                   ,t.end_nodal_network_code
                   ,t.end_nodal_network_name
                   ,t.dt
                from (
                        select
                            t.lianxu_name  --连续站点拼接

                           ,t.lianxu_flg
                           ,t.waybill_no
                           ,t.taking_early_scansitecode --揽收网点
                           ,t.taking_name
                           ,t.taking_time
                           ,t.taking_network_type               --揽收网点类型，网点类型:1-总部,2-代理区,3-加盟商,4-中心,5-集散点,6-网点',
                           ,t.taking_fran_code                  --揽收网点加盟商
                           ,t.taking_fran_name                   --揽收网点加盟商code
                           ,t.taking_agent_code                  --'代理区编码',
                           ,t.taking_agent_name                 --'代理区名称,',
                           ,t.taking_manage_code                --'管理大区名称',
                           ,t.taking_manage_name                --'管理大区名称',
                           ,t.taking_provider_id                --'省份ID',
                           ,t.taking_provider_desc            --'省份',
                           ,t.taking_city_id                   --'城市ID',
                           ,t.taking_city_desc                 --'城市',
                           ,t.taking_area_id                   --'区/县ID',
                           ,t.taking_area_desc                 --'区/县',
                           ,t.sign_network_code as  sign_network_code   --签收网点
                           ,t.sign_network_name as   sign_network_name  --签收网点
                           ,t.sign_network_type sign_network_type
                           ,t.sign_time as sign_time                    --签收时间
                           ,t.sign_fran_code    sign_fran_code             --签收网点加盟商
                           ,t.sign_fran_name    sign_fran_name              --签收网点加盟商code
                           ,t.sign_agent_code   sign_agent_code             --'代理区编码',
                           ,t.sign_agent_name   sign_agent_name             --'代理区名称,',
                           ,t.sign_manage_code  sign_manage_code            --'管理大区名称',
                           ,t.sign_manage_name  sign_manage_name            --'管理大区名称',
                           ,t.sign_provider_id  sign_provider_id            --'省份ID',
                           ,t.sign_provider_desc sign_provider_desc         --'省份',
                           ,t.sign_city_id       sign_city_id                  --'城市ID',
                           ,t.sign_city_desc     sign_city_desc               --'城市',
                           ,t.sign_area_id       sign_area_id                   --'区/县ID',
                           ,t.sign_area_desc     sign_area_desc                --'区/县',
                           ,t.first_center_network_code --始发中心
                           ,t.first_center_network_name
                           ,t.end_center_network_code --末端中心
                           ,t.end_center_network_name
                           ,t.send_station_network_code  --发件网点
                           ,t.send_station_simple_name
                           ,t.send_station_network_name
                           ,t.send_flg
                           ,t.send_station_agent_code    --发件网点代理区
                           ,t.send_station_network_type -- 发件网点类型
                           ,t.step --站点排序
                           ,t.scan_time   --发件网点扫描时间
                           ,t.examine_time --退转件取消时间
                           ,t.terminal_dispatch_code --原三段码
                           ,t.terminal_network_code --原目的地
                           ,t.terminal_network_name
                           ,t.first_center_time --始发中心扫描时间
                           ,t.end_center_time   --末端中心扫描时间
                           ,t.sj_agent_in_count
                           ,t.sj_agent_out_count
                           ,t.step-sum(t.lianxu_flg ) over(partition by t.waybill_no order by t.step) as lianxu_sum
                           ,t.step_flg
                           ,max(case when t.step_flg=2 then t.send_station_network_code else null end )over(partition by t.waybill_no ) as step_site
                           ,t.must_center_name
                           ,t.first_nodal_network_code
                           ,t.first_nodal_network_name
                           ,t.end_nodal_network_code
                           ,t.end_nodal_network_name
                           ,t.dt
                        from  bill_base_shiji_tmp_step1 t
                ) t
            ) t


)


--取实际路由,补全问题件，签收网点等信息
,bill_base_shij_res as (
select
     t.waybill_no
    ,max(t.taking_early_scansitecode)  as taking_early_scansitecode --揽收网点
    ,max(t.taking_name) taking_name
    ,max(t.taking_time) taking_time
    ,SUBSTR(max(t.taking_time),1,10) as taking_time_f
    ,max(t.taking_network_type)   taking_network_type         --揽收网点类型，网点类型:1-总部,2-代理区,3-加盟商,4-中心,5-集散点,6-网点',
    ,max(t.taking_fran_code)     taking_fran_code             --揽收网点加盟商
    ,max(t.taking_fran_name)    taking_fran_name              --揽收网点加盟商code
    ,max(t.taking_agent_code)   taking_agent_code             --'代理区编码',
    ,max(t.taking_agent_name)   taking_agent_name             --'代理区名称,',
    ,max(t.taking_manage_code)  taking_manage_code            --'管理大区名称',
    ,max(t.taking_manage_name)  taking_manage_name            --'管理大区名称',
    ,max(t.taking_provider_id)  taking_provider_id            --'省份ID',
    ,max(t.taking_provider_desc) taking_provider_desc         --'省份',
    ,max(t.taking_city_id)    taking_city_id                  --'城市ID',
    ,max(t.taking_city_desc)   taking_city_desc               --'城市',
    ,max(t.taking_area_id)   taking_area_id                   --'区/县ID',
    ,max(t.taking_area_desc)  taking_area_desc                --'区/县',
    ,max(t.first_center_network_code) as first_center_network_code  --始发中心
    ,max(t.first_center_network_name) as first_center_network_name
    ,max(t.end_center_network_code) as end_center_network_code  --末端中心
    ,max(t.end_center_network_name) as end_center_network_name
    ,max(t.sign_network_code) as  sign_network_code   --签收网点
    ,max(t.sign_network_name) as   sign_network_name  --签收网点
    ,max(t.sign_network_type) sign_network_type
    ,max(t.sign_time) as sign_time                    --签收时间
    ,max(t.sign_fran_code)    sign_fran_code             --签收网点加盟商
    ,max(t.sign_fran_name)    sign_fran_name              --签收网点加盟商code
    ,max(t.sign_agent_code)   sign_agent_code             --'代理区编码',
    ,max(t.sign_agent_name)   sign_agent_name             --'代理区名称,',
    ,max(t.sign_manage_code)  sign_manage_code            --'管理大区名称',
    ,max(t.sign_manage_name)  sign_manage_name            --'管理大区名称',
    ,max(t.sign_provider_id)  sign_provider_id            --'省份ID',
    ,max(t.sign_provider_desc) sign_provider_desc         --'省份',
    ,max(t.sign_city_id)       sign_city_id                  --'城市ID',
    ,max(t.sign_city_desc)     sign_city_desc               --'城市',
    ,max(t.sign_area_id)       sign_area_id                   --'区/县ID',
    ,max(t.sign_area_desc)     sign_area_desc                --'区/县',
    ,max(t.terminal_dispatch_code) as terminal_dispatch_code --原三段码
    ,max(t.terminal_network_code) as terminal_network_code--原目的地
    ,max(t.terminal_network_name) as terminal_network_name
    ,if(max(t.send_flg1)=0,concat('sjly',rand()), concat_ws('-',sort_array(collect_list(t.ws_send_station_network_name))))  as sj_route1  --实际路由
    ,if(max(t.send_flg1)=0,concat('sjly',rand()), concat_ws('-',sort_array(collect_list(t.ws_send_station_simple_name))))  as sj_route_simple_name1  --实际路由

    ,if(max(t.send_flg1)=0,concat('sjly',rand()), concat_ws('-',sort_array(collect_list(if(t.send_flg=1,
                if(t.step_flg=1 and t.step_site_flg =1 and t.lianxu_flg=1  ,null,t.ws_send_station_network_name)
                ,null)))))  as sj_route  --实际路由

    ,if(max(t.send_flg1)=0,concat('sjly',rand()), concat_ws('-',sort_array(collect_list(if(t.send_flg=1,
                if(t.step_flg=1 and t.step_site_flg =1  and t.lianxu_flg=1 ,null,t.ws_send_station_simple_name)
                ,null)))))  as sj_route_simple_name     --实际路由


    ,max(t.lianxu_name1) lianxu_name --连续出现的路由
    ,max(t4.proble_type_subject_code) as proble_type_subject_code --问题件类型
    ,max(t4.proble_type_subject_name) as proble_type_subject_name
    ,if(max(t.terminal_network_code)='9999-99-99',max(t.sign_network_code),max(t.terminal_network_code))  as join_end_code

    ,sum(if(t.send_flg=1,
                if(t.step_flg=1 and t.step_site_flg =1 and t.lianxu_flg=1  ,0,if(t.send_station_agent_code = t.taking_agent_code,1,0))
                ,0))  as sj_agent_in_count

    ,sum(if(t.send_flg=1,
                if(t.step_flg=1 and t.step_site_flg =1 and t.lianxu_flg=1  ,0,if(t.send_station_agent_code <> t.taking_agent_code,1,0))
                ,0)) as sj_agent_out_count

    ,max(t.lianxu_sum1) as lianxu_sum1
    ,max(t.send_flg1) as send_flg
    ,max(t.must_center_name) as must_center_name
    ,max(t.first_nodal_network_code) first_nodal_network_code
    ,max(t.first_nodal_network_name) first_nodal_network_name
    ,max(t.end_nodal_network_code) end_nodal_network_code
    ,max(t.end_nodal_network_name) end_nodal_network_name
    ,t.dt
from bill_base_shiji_tmp_step2 t
left join bill_problem_base t4 on  t.waybill_no=t4.waybill_no       --问题件

group by  t.waybill_no,t.dt
)


--取规划路由
,bill_base_fast as (
    select
         t.in_from_code --始发网点
        ,t.out_to_code  --目的网点
        ,t.whole_route  --中转路由
        ,t.total_time_use --全程时效
        ,t.total_days_t  --全程耗时
        ,t.is_main_route --路由类型
        ,t.out_from_name
        ,t.out_from_code
        ,t.in_to_name
        ,t.in_to_code
        ,t.dt
    from
        (
            select
             t.in_from_code --始发网点
            ,t.out_to_code  --目的网点
            ,t.whole_route   --中转路由,同城件，或者直发取始发中心
            ,t.total_time_use --全程时效
            ,t.total_days_t  --全程耗时
            ,t.is_main_route --路由类型
            ,t.dt
            ,t.in_collect_code
            ,t.out_from_name
            ,t.out_from_code
            ,t.in_to_name
            ,t.in_to_code
            ,row_number()over(partition by t.dt,t.in_from_code,t.out_to_code  order by t.total_days_t ,t.total_time_use ) as rn
            from jms_dm.dm_route_recommend t

            --dm_route_fastest
            where t.dt>='{{ execution_date | date_add(-31) | cst_ds }}' and t.dt<='{{ execution_date  | cst_ds }}'

        )t

        where t.rn=1
)

--取规划路由
,bill_base_fast_center as (
    select
         t.in_to_code --始发网点
        ,t.out_from_code  --目的网点
        ,t.whole_route  --中转路由
        ,t.dt
    from
        (
            select
             t.in_to_code --始发网点
            ,t.out_from_code  --目的网点
            ,t.whole_route   --中转路由,同城件，或者直发取始发中心
            ,t.dt
            ,row_number()over(partition by t.dt,t.in_to_code,t.out_from_code  order by t.total_days_t ,t.total_time_use ) as rn
            from jms_dm.dm_route_recommend t

            --dm_route_fastest
            where t.dt>='{{ execution_date | date_add(-31) | cst_ds }}' and t.dt<='{{ execution_date | cst_ds }}'
        )t

        where t.rn=1
)

--取备用路由
,dm_route_whole as (
         select
             t.start_code --始发网点
            ,t.end_code  --目的网点
            ,t.route_name  --中转路由
            ,to_date(t.start_date) as start_date
            ,to_date(t.end_date) as end_date
          from jms_dim.dim_yl_tms_route_base_dt t

          where route_type=2  and status=1 and dt='{{ execution_date | cst_ds }}'

          group by
                 t.start_code --始发网点
                ,t.end_code  --目的网点
                ,t.route_name  --中转路由
                ,to_date(t.start_date)
                ,to_date(t.end_date)

)
,bill_base_tmp5 as (
    select
         t.dt as date_time
        ,t.waybill_no waybill_no
        ,t.taking_early_scansitecode  taking_early_scansitecode   --揽收网点
        ,t.taking_name taking_name
        ,t.taking_network_type    taking_network_type        --揽收网点类型，网点类型:1-总部,2-代理区,3-加盟商,4-中心,5-集散点,6-网点',
        ,t.taking_fran_code        taking_fran_code          --揽收网点加盟商
        ,t.taking_fran_name      taking_fran_name            --揽收网点加盟商code
        ,t.taking_agent_code     taking_agent_code           --'代理区编码',
        ,t.taking_agent_name    taking_agent_name            --'代理区名称,',
        ,t.taking_manage_code   taking_manage_code           --'管理大区名称',
        ,t.taking_manage_name   taking_manage_name           --'管理大区名称',
        ,t.taking_provider_id     taking_provider_id         --'省份ID',
        ,t.taking_provider_desc   taking_provider_desc       --'省份',
        ,t.taking_city_id      taking_city_id              --'城市ID',
        ,t.taking_city_desc    taking_city_desc              --'城市',
        ,t.taking_area_id      taking_area_id                --'区/县ID',
        ,t.taking_area_desc    taking_area_desc              --'区/县',
        ,t.taking_time     taking_time --揽收时间
        ,t.first_center_network_code first_center_network_code --始发中心
        ,t.first_center_network_name first_center_network_name
        ,t.end_center_network_code end_center_network_code --末端中心
        ,t.end_center_network_name end_center_network_name
        ,t.sign_network_code sign_network_code --签收网点
        ,t.sign_network_name  sign_network_name --签收网点
        ,t.sign_network_type sign_network_type
        ,t.sign_fran_code   sign_fran_code              --签收网点加盟商
        ,t.sign_fran_name     sign_fran_name            --签收网点加盟商code
        ,t.sign_agent_code     sign_agent_code         --'代理区编码',
        ,t.sign_agent_name    sign_agent_name           --'代理区名称,',
        ,t.sign_manage_code    sign_manage_code         --'管理大区名称',
        ,t.sign_manage_name    sign_manage_name         --'管理大区名称',
        ,t.sign_provider_id     sign_provider_id        --'省份ID',
        ,t.sign_provider_desc  sign_provider_desc      --'省份',
        ,t.sign_city_id       sign_city_id                 --'城市ID',
        ,t.sign_city_desc    sign_city_desc             --'城市',
        ,t.sign_area_id       sign_area_id               --'区/县ID',
        ,t.sign_area_desc      sign_area_desc               --'区/县',
        ,t.sign_time          sign_time          --签收时间
        ,if(t.terminal_dispatch_code='9999-99-99',null , terminal_dispatch_code)  as terminal_dispatch_code--原三段码
        ,if(t.terminal_network_code='9999-99-99',null , terminal_network_code)   as terminal_network_code --原目的地
        ,if(t.terminal_network_name='9999-99-99',null , terminal_network_name)  as terminal_network_name --原目的地
        ,if(locate('sjly', t.sj_route)>=1,null ,regexp_replace(t.sj_route,'\\d+\:',''))  as sj_route  --实际路由
        ,if(locate('sjly', t.sj_route_simple_name)>=1,null ,regexp_replace(t.sj_route_simple_name,'\\d+\:',''))   as sj_route_simple_name  --实际路由
        ,if(locate('sjly', t.sj_route_simple_name)>=1,0 ,size(split(regexp_replace(t.sj_route,'\\d+\:',''),'-')))   as sj_route_num
        ,if(lianxu_sum1>=2,t.lianxu_name,null) AS lianxu_name --连续出现的路由
        ,if(lianxu_sum1>=2,size(split(t.lianxu_name,'-')),0) as lianxu_name_num
        ,t.proble_type_subject_code proble_type_subject_code --问题件类型
        ,t.proble_type_subject_name  proble_type_subject_name--问题件名称
        ,t.sj_agent_in_count sj_agent_in_count
        ,t.sj_agent_out_count sj_agent_out_count
        ,nvl(nvl(t2.whole_route,t1.whole_route) ,nvl(t3.route_name,t4.route_name)) as gh_route

       ,if((nvl(nvl(t2.whole_route,t1.whole_route) ,nvl(t3.route_name,t4.route_name)) is null )
        or ( nvl(nvl(t2.whole_route,t1.whole_route) ,nvl(t3.route_name,t4.route_name)) ) = '', if( (t1.in_to_code is null or t1.in_to_code ='')  ,0,1),
            size(split( nvl(nvl(t2.whole_route,t1.whole_route) ,nvl(t3.route_name,t4.route_name)),'-')))  as gh_route_num
        ,nvl(t1.in_to_code ,t4.start_code) as start_code_flg
        ,nvl(t3.route_name,t4.route_name) as bak_route_name
        ,nvl(t2.whole_route,t1.whole_route) as gh_route_flg
        ,t3.route_name as bak_route_name1
        ,t4.route_name as bak_route_name2
        ,t.must_center_name must_center_name
        ,t.first_nodal_network_code first_nodal_network_code
        ,t.first_nodal_network_name first_nodal_network_name
        ,t.end_nodal_network_code
        ,t.end_nodal_network_name
        ,t6.whole_route as center_whole_route
        --,case when t5.rn=1 then t5.end_name else t5.all_map[end_center_network_code] end    must_center_name
         ,t1.out_from_name   as   gh_end_center_name
        ,t1.out_from_code   as   gh_end_center_code
        ,t1.in_to_name      as   gh_first_center_name
        ,t1.in_to_code      as   gh_first_center_code
        ,t.dt
    from bill_base_shij_res t
    left join bill_base_fast t1 on t.taking_early_scansitecode =t1.in_from_code
         and t.join_end_code=t1.out_to_code
         and to_date(t.taking_time)=t1.dt

    left join dm_route_whole t4 on t.first_center_network_code =t4.start_code
         and t.end_center_network_code=t4.end_code
         and to_date(t.taking_time)>=t4.start_date
         and to_date(t.taking_time)<=t4.end_date

    left join bill_base_fast t2 on t.taking_early_scansitecode =t2.in_from_code
         and t.join_end_code=t2.out_to_code
         and regexp_replace(t.sj_route_simple_name,'\\d+\:','')=t2.whole_route
         and to_date(t.taking_time)=t2.dt

    left join dm_route_whole t3 on t.first_center_network_code =t3.start_code
         and t.end_center_network_code=t3.end_code
         and regexp_replace(t.sj_route_simple_name,'\\d+\:','')=t3.route_name
         and to_date(t.taking_time)>=t3.start_date
         and to_date(t.taking_time)<=t3.end_date

    left join bill_base_fast_center t6 on t.first_center_network_code =t6.in_to_code
         and t.end_center_network_code=t6.out_from_code
         and regexp_replace(t.sj_route_simple_name,'\\d+\:','')=t6.whole_route
         and to_date(t.taking_time)=t1.dt


    where t.dt='{{ execution_date |  cst_ds }}'


         and t.taking_network_type in ('5','6')
         and  t.taking_early_scansitecode is not null
         and t.sign_network_code  is not null
)

insert overwrite table jms_dm.dm_all_transfer_count_deatil_dt partition ( dt )
select
     t.dt as date_time
    ,max(t.waybill_no) waybill_no
    ,max(t.taking_early_scansitecode ) taking_early_scansitecode   --揽收网点
    ,max(t.taking_name) taking_name
    ,max(t.taking_network_type )   taking_network_type        --揽收网点类型，网点类型:1-总部,2-代理区,3-加盟商,4-中心,5-集散点,6-网点',
    ,max(t.taking_fran_code   )     taking_fran_code          --揽收网点加盟商
    ,max(t.taking_fran_name   )   taking_fran_name            --揽收网点加盟商code
    ,max(t.taking_agent_code  )   taking_agent_code           --'代理区编码',
    ,max(t.taking_agent_name  )  taking_agent_name            --'代理区名称,',
    ,max(t.taking_manage_code )  taking_manage_code           --'管理大区名称',
    ,max(t.taking_manage_name )  taking_manage_name           --'管理大区名称',
    ,max(t.taking_provider_id   )  taking_provider_id         --'省份ID',
    ,max(t.taking_provider_desc )  taking_provider_desc       --'省份',
    ,max(t.taking_city_id   )   taking_city_id              --'城市ID',
    ,max(t.taking_city_desc )   taking_city_desc              --'城市',
    ,max(t.taking_area_id  )    taking_area_id                --'区/县ID',
    ,max(t.taking_area_desc )   taking_area_desc              --'区/县',
    ,max(t.taking_time  )   taking_time --揽收时间
    ,max(t.first_center_network_code) first_center_network_code --始发中心
    ,max(t.first_center_network_name) first_center_network_name
    ,max(t.end_center_network_code) end_center_network_code --末端中心
    ,max(t.end_center_network_name) end_center_network_name
    ,max(t.sign_network_code) sign_network_code --签收网点
    ,max(t.sign_network_name)  sign_network_name --签收网点
    ,max(t.sign_network_type) sign_network_type
    ,max(t.sign_fran_code)   sign_fran_code              --签收网点加盟商
    ,max(t.sign_fran_name )    sign_fran_name            --签收网点加盟商code
    ,max(t.sign_agent_code)     sign_agent_code         --'代理区编码',
    ,max(t.sign_agent_name )   sign_agent_name           --'代理区名称,',
    ,max(t.sign_manage_code)    sign_manage_code         --'管理大区名称',
    ,max(t.sign_manage_name )   sign_manage_name         --'管理大区名称',
    ,max(t.sign_provider_id )    sign_provider_id        --'省份ID',
    ,max(t.sign_provider_desc ) sign_provider_desc      --'省份',
    ,max(t.sign_city_id   )    sign_city_id                 --'城市ID',
    ,max(t.sign_city_desc  )  sign_city_desc             --'城市',
    ,max(t.sign_area_id   )    sign_area_id               --'区/县ID',
    ,max(t.sign_area_desc )     sign_area_desc               --'区/县',
    ,max(t.sign_time     )     sign_time          --签收时间
    ,max(t.terminal_dispatch_code)  as terminal_dispatch_code--原三段码
    ,max(t.terminal_network_code )  as terminal_network_code --原目的地
    ,max(t.terminal_network_name)  as terminal_network_name --原目的地
    ,max(t.sj_route) as sj_route  --实际路由
    ,max(t.sj_route_simple_name)   as sj_route_simple_name  --实际路由
    ,max(t.sj_route_num)   as sj_route_num
    ,concat(max(t.lianxu_name),'*',max(bak_route_name1),'*',max(bak_route_name2)) lianxu_name --连续出现的路由
    ,max(t.lianxu_name_num) as lianxu_name_num
    ,max(t.proble_type_subject_code) proble_type_subject_code --问题件类型
    ,max(t.proble_type_subject_name ) proble_type_subject_name--问题件名称
    ,max(t.sj_agent_in_count) sj_agent_in_count
    ,max(t.sj_agent_out_count) sj_agent_out_count
    ,max(t.gh_route) as gh_route
    ,max(t.gh_route_num) as gh_route_num
    ,max(case when  !array_contains(split(t.must_center_name,'-'),first_center_network_name)
                       and t.sj_route_num > if(t.gh_route is null,9999,gh_route_num )
                       and must_center_name <> '' then 0
              when  t.terminal_dispatch_code is not null  then 0
              when  t.proble_type_subject_code is not null then 0
              when  (t.gh_route is  null or t.gh_route ='') and (t.bak_route_name is null or t.bak_route_name ='') and (t.center_whole_route is null or t.center_whole_route='')  then 0
              when  t.sj_route_num > if(t.gh_route is null,9999,gh_route_num) then
                    if( t.bak_route_name is not null and (t.bak_route_name1=t.sj_route_simple_name  or t.bak_route_name2=t.sj_route_simple_name ) , 0 , if(t.center_whole_route=t.sj_route_simple_name,0,1) )
              else 0 end

              ) as is_rational_flg --合理


     , max(case   when  t.terminal_dispatch_code is not null and t.sj_route_num > if(t.gh_route is null,9999,gh_route_num) then 1 else 0 end
           ) is_reback_flg



         ,max(case when  !array_contains(split(t.must_center_name,'-'),first_center_network_name)
                       and t.sj_route_num > if(t.gh_route is null,9999,gh_route_num )
                       and must_center_name <> '' then 0
                   when  t.terminal_dispatch_code is not null  then 0
                   when  t.proble_type_subject_code is not null and t.sj_route_num > if(t.gh_route is null,9999,gh_route_num) then 1 else 0 end
             )   is_proble_flg


      --错发

         --,max(case when  t.start_code_flg is not null
             --       and t.start_code_flg<>t.first_center_network_code
             --       and t.sj_route_num > if(t.gh_route is null,9999,gh_route_num ) then 1 else 0 end )
         --is_wrong_flg
        ,max( case  when  t.terminal_dispatch_code is not null and t.sj_route_num > if(t.gh_route is null,9999,gh_route_num) then 0
                    when !array_contains(split(t.must_center_name,'-') ,first_center_network_name)
                       and t.sj_route_num > if(t.gh_route is null,9999,gh_route_num )
                       and must_center_name <> ''
                  then 1 else 0 end  ) as is_wrong_flg
    --备用路由
    ,max(t.bak_route_name)   as bak_route

    ,if(max(t.gh_route_flg)<> max(t.sj_route_simple_name) and max(t.sj_route_simple_name)=max(t.bak_route_name),
        size(split( max(t.bak_route_name),'-' )), 0 ) as bak_route_num

    ,if(max(t.gh_route_flg)<> max(t.sj_route_simple_name) and max(t.sj_route_simple_name)=max(t.bak_route_name),1 , 0 ) as is_bak_route
    ,max(must_center_name) as must_center_name
    ,max(t.first_nodal_network_code) first_nodal_network_code
    ,max(t.first_nodal_network_name) first_nodal_network_name
    ,max(t.end_nodal_network_code) end_nodal_network_code
    ,max(t.end_nodal_network_name) end_nodal_network_name
    ,max(t.center_whole_route) center_whole_route
    ,max(t.gh_first_center_name) as gh_first_center_name
    ,max(t.gh_first_center_code) as gh_first_center_code
    ,max(t.gh_end_center_name) as gh_end_center_name
    ,max(t.gh_end_center_code) as gh_end_center_code
        ,max(case when  !array_contains(split(t.must_center_name,'-'),first_center_network_name)
                       and t.sj_route_num > if(t.gh_route is null,9999,gh_route_num )
                       and must_center_name <> '' then 0
              when  t.terminal_dispatch_code is not null  then 0
              when  t.proble_type_subject_code is not null then 0
              when  t.sj_route_num > if(t.gh_route is null,9999,gh_route_num) then
                    if( t.bak_route_name is not null and (t.bak_route_name1=t.sj_route_simple_name  or t.bak_route_name2=t.sj_route_simple_name ) , 0 ,
                    if(t.center_whole_route=t.sj_route_simple_name,0,if(t.bak_route_name is null and t.center_whole_route is null and t.gh_route_flg is null,0,0)) )
              when  t.gh_route_num <> 1 and t.gh_first_center_code is  null then 1
              else 0 end

              ) as is_route_error_flg --串联失败

    ,max(case when  !array_contains(split(t.must_center_name,'-'),first_center_network_name)
                       and t.sj_route_num > if(t.gh_route is null,9999,gh_route_num )
                       and must_center_name <> '' then 0
              when  t.terminal_dispatch_code is not null  then 0
              when  t.proble_type_subject_code is not null then 0
              when  t.sj_route_num > if(t.gh_route is null,9999,gh_route_num) then
                    if( t.bak_route_name is not null and (t.bak_route_name1=t.sj_route_simple_name  or t.bak_route_name2=t.sj_route_simple_name ) , 0 ,
                    if(t.center_whole_route=t.sj_route_simple_name,0,if(t.bak_route_name is null and t.center_whole_route is null and t.gh_route_flg is null,0,0)) )
              when t.gh_route is null  or t.gh_route ='' then 0
              when if( size(split(t.must_center_name,'-')) >1 ,
                             if(size(split(t.must_center_name,'-')) >2,(split(t.must_center_name,'-')[0] <> split(t.must_center_name,'-')[1] or
                                split(t.must_center_name,'-')[0] <> split(t.must_center_name,'-')[2]
                                or split(t.must_center_name,'-')[1] <> split(t.must_center_name,'-')[2]  ) and (t.gh_first_center_code <> t.first_center_network_code)
                             ,split(t.must_center_name,'-')[0] <> split(t.must_center_name,'-')[1] and (t.gh_first_center_code <> t.first_center_network_code)),FALSE)

                                  then 1
              else 0 end


)              as is_two_must_flg --双向交件
    ,t.dt
from bill_base_tmp5 t
group by t.dt,
t.waybill_no


 distribute by pmod(hash(rand()), 176);